using System;
using System.Collections.Generic;
using System.Text;
using System.Data.OleDb;
using System.Data;
using DTO;
namespace DAO
{
  public   class TheDocGiaDAO
    {
      public DataTable laydsthedocgia_DAO()
      {
          DataTable kq = new DataTable();
          string sql = "select TheDocGia.MaDocGia as ID,TheDocGia.HoTen as HoTen, LoaiDocGia.TenLoaiDocGia as LoaiDocGia,TheDocGia.NgaySinh as NgaySinh,TheDocGia.DiaChi as DiaChi,TheDocGia.Email as Email,TheDocGia.NgayLapThe as NgayLapThe,NhanVien.HoTen as NguoiLap,LoaiDocGia.MaLoaiDocGia as MLDG from TheDocGia,NhanVien,LoaiDocGia  where NhanVien.MaNhanVien=TheDocGia.NguoiLap and TheDocGia.LoaiDocGia=LoaiDocGia.MaLoaiDocGia";
          kq = DataProvider.ExcuteQuery(sql);
          return kq;

      }
      public bool ThemTheDocGia_DAO(TheDocGiaDTO theDocGiaDTO)
      {
          string sql = "insert into TheDocGia(HoTen,LoaiDocGia,NgaySinh,DiaChi,Email,NgayLapThe,NguoiLap) values('";
          sql += theDocGiaDTO.Hoten + "',";
              sql+= theDocGiaDTO.Loaidocgia.ToString() + ",'";
          sql+= theDocGiaDTO.Ngaysinh.ToString() + "','";
          sql += theDocGiaDTO.Diachi + "','" + theDocGiaDTO.Email + "','";
          sql += theDocGiaDTO.Ngaylapthe.ToString() + "'," + theDocGiaDTO.Nguoilap.ToString() + ")";
          try { DataProvider.ExecutenonQuery(sql); }
          catch (Exception ex)
          { return false; }
          return true;
      }

      public static bool CapNhatTheDocGia_DAO(TheDocGiaDTO tdgDTO)
      {
          string sql = "Update TheDocGia set HoTen='" + tdgDTO.Hoten + "',";
          sql += "LoaiDocGia=" + tdgDTO.Loaidocgia.ToString() + ",NgaySinh='" + tdgDTO.Ngaysinh.ToString() + "',";
          sql += "DiaChi='" + tdgDTO.Diachi + "',Email='" + tdgDTO.Email + "',NgayLapThe='" + tdgDTO.Ngaylapthe.ToString() + "',NguoiLap=" + tdgDTO.Nguoilap;
          sql += " where ID=" + tdgDTO.Madocgia.ToString();
          try
          {
              DataProvider.ExecutenonQuery(sql);
          }
          catch
          {
              return false;
          }
          return true;
      }

      public static bool XoaTheDocGia_DAO(int id)
      {
          string sql = "delete from TheDocGia where ID=" + id.ToString();
          try
          {
              DataProvider.ExecutenonQuery(sql);
          }
          catch
          {
              return false;
          }
          return true;
      }

      public static DataTable TimTheDocGiaTheoTen_DAO(string ten)
      {
          DataTable kq = new DataTable();
          string sql = "select TheDocGia.MaDocGia as ID,TheDocGia.HoTen as HoTen, LoaiDocGia.TenLoaiDocGia as LoaiDocGia,TheDocGia.NgaySinh as NgaySinh,TheDocGia.DiaChi as DiaChi,TheDocGia.Email as Email,TheDocGia.NgayLapThe as NgayLapThe,NhanVien.HoTen as NguoiLap,LoaiDocGia.MaLoaiDocGia as MLDG from TheDocGia,NhanVien,LoaiDocGia  where NhanVien.MaNhanVien=TheDocGia.NguoiLap and TheDocGia.LoaiDocGia=LoaiDocGia.MaLoaiDocGia and TheDocGia.HoTen like '%"+ten+"%'";
          kq = DataProvider.ExcuteQuery(sql);
          return kq;
      }
    }
}
